//
// Source code recreated from ConfigVo .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.ht.modules.system.online.cgreport.util;

import java.text.MessageFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import jakarta.servlet.http.HttpServletRequest;
import com.ht.utils.oConvertUtils;

public class SqlUtil {
    public static final String DATABSE_TYPE_MYSQL = "mysql";
    public static final String DATABSE_TYPE_POSTGRE = "postgresql";
    public static final String DATABSE_TYPE_ORACLE = "oracle";
    public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";
    public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";
    public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";
    public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";
    public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";
    public static final String MYSQL_ALLTABLES_SQL = "select distinct table_name from information_schema.columns where table_schema = {0}";
    public static final String POSTGRE__ALLTABLES_SQL = "SELECT distinct c.relname AS  table_name FROM pg_class c";
    public static final String ORACLE__ALLTABLES_SQL = "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
    public static final String SQLSERVER__ALLTABLES_SQL = "select distinct c.name as  table_name from sys.objects c";
    public static final String MYSQL_ALLCOLUMNS_SQL = "select column_name from information_schema.columns where table_name = {0} and table_schema = {1}";
    public static final String POSTGRE_ALLCOLUMNS_SQL = "select table_name from information_schema.columns where table_name = {0}";
    public static final String ORACLE_ALLCOLUMNS_SQL = "select column_name from all_tab_columns where table_name ={0}";
    public static final String SQLSERVER_ALLCOLUMNS_SQL = "select name from syscolumns where id={0}";

    public SqlUtil() {
    }

    public static String getFullSql(String sql, Map params) {
        StringBuilder sqlB = new StringBuilder();
        sqlB.append("SELECT t.* FROM ( ");
        sqlB.append(sql + " ");
        sqlB.append(") t ");
        if (params != null && params.size() >= 1) {
            sqlB.append("WHERE 1=1  ");
            Iterator it = params.keySet().iterator();

            while(it.hasNext()) {
                String key = String.valueOf(it.next());
                String value = String.valueOf(params.get(key));
                if (oConvertUtils.isNotEmpty(value)) {
                    sqlB.append(" AND ");
                    sqlB.append(" " + key + value);
                }
            }
        }

        return sqlB.toString();
    }

    public static String getCountSql(String sql, Map params) {
        String querySql = getFullSql(sql, params);
        querySql = "SELECT COUNT(*) COUNT FROM (" + querySql + ") t2";
        return querySql;
    }

    public static String jeecgCreatePageSql(String sql, Map params, int page, int rows) {
        String dbTypeUrl = "jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false";
        int beginNum = (page - 1) * rows;
        String[] sqlParam = new String[]{sql, beginNum + "", rows + ""};
        if (dbTypeUrl.indexOf("mysql") != -1) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {1},{2}", sqlParam);
        } else if (dbTypeUrl.indexOf("postgresql") != -1) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", sqlParam);
        } else {
            int beginIndex = (page - 1) * rows;
            int endIndex = beginIndex + rows;
            sqlParam[2] = Integer.toString(beginIndex);
            sqlParam[1] = Integer.toString(endIndex);
            if (dbTypeUrl.indexOf("oracle") != -1) {
                sql = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", sqlParam);
            } else if (dbTypeUrl.indexOf("sqlserver") != -1) {
                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
                sql = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", sqlParam);
            }
        }

        return sql;
    }

    public static String jeecgCreatePageSql(String dbTypeUrl, String dbKey, String sql, Map params, int page, int rows) {
        sql = getFullSql(sql, params);
        int beginNum = (page - 1) * rows;
        String[] sqlParam = new String[]{sql, beginNum + "", rows + ""};
        String databaseType = "";
        if ("mysql".equalsIgnoreCase(databaseType)) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {1},{2}", sqlParam);
        } else if ("postgresql".equalsIgnoreCase(databaseType)) {
            sql = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", sqlParam);
        } else {
            int beginIndex = (page - 1) * rows;
            int endIndex = beginIndex + rows;
            sqlParam[2] = Integer.toString(beginIndex);
            sqlParam[1] = Integer.toString(endIndex);
            if ("oracle".equalsIgnoreCase(databaseType)) {
                sql = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", sqlParam);
            } else if ("sqlserver".equalsIgnoreCase(databaseType)) {
                sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
                sql = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", sqlParam);
            }
        }

        return sql;
    }

    private static int getAfterSelectInsertPoint(String sql) {
        int selectIndex = sql.toLowerCase().indexOf("select");
        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
        return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
    }

    public static String getAllTableSql(String dbType, String... param) {
        if (oConvertUtils.isNotEmpty(dbType)) {
            if ("mysql".equals(dbType)) {
                return MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", param);
            }

            if ("oracle".equals(dbType)) {
                return "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
            }

            if ("postgresql".equals(dbType)) {
                return "SELECT distinct c.relname AS  table_name FROM pg_class c";
            }

            if ("sqlserver".equals(dbType)) {
                return "select distinct c.name as  table_name from sys.objects c";
            }
        }

        return null;
    }

    public static String getAllCloumnSql(String dbType, String... param) {
        if (oConvertUtils.isNotEmpty(dbType)) {
            if ("mysql".equals(dbType)) {
                return MessageFormat.format("select column_name from information_schema.columns where table_name = {0} and table_schema = {1}", param);
            }

            if ("oracle".equals(dbType)) {
                return MessageFormat.format("select column_name from all_tab_columns where table_name ={0}", param);
            }

            if ("postgresql".equals(dbType)) {
                return MessageFormat.format("select table_name from information_schema.columns where table_name = {0}", param);
            }

            if ("sqlserver".equals(dbType)) {
                return MessageFormat.format("select name from syscolumns where id={0}", param);
            }
        }

        return null;
    }

    public static String getSql(String sql) {
        String regex = "\\$\\{\\w+\\}";
        Pattern p = Pattern.compile(regex);

        Matcher m;
        String whereParam;
        for(m = p.matcher(sql); m.find(); sql = sql.replace("'''", "''")) {
            whereParam = m.group();
            sql = sql.replace(whereParam, "'' or 1=1 or 1=''");
        }

        regex = "\\{\\w+\\}";
        p = Pattern.compile(regex);

        for(m = p.matcher(sql); m.find(); sql = sql.replace(whereParam, " 1=1 ")) {
            whereParam = m.group();
        }

        return sql;
    }

    public static Map<String, Object> getParameterMap(HttpServletRequest request) {
        Map<?, ?> properties = request.getParameterMap();
        Map<String, Object> returnMap = new HashMap();
        Iterator<?> entries = properties.entrySet().iterator();
        String name = "";
        String value = "";

        for(Object valueObj = null; entries.hasNext(); returnMap.put(name, value)) {
            Entry<String, Object> entry = (Entry)entries.next();
            name = (String)entry.getKey();
            valueObj = entry.getValue();
            if (!"_t".equals(name) && null != valueObj) {
                if (!(valueObj instanceof String[])) {
                    value = valueObj.toString();
                } else {
                    String[] values = (String[])((String[])valueObj);

                    for(int i = 0; i < values.length; ++i) {
                        value = values[i] + ",";
                    }

                    value = value.substring(0, value.length() - 1);
                }
            } else {
                value = "";
            }
        }

        return returnMap;
    }
}
